from flask import Flask, render_template, request
import pymysql

app = Flask(__name__)

# 数据库连接信息，请根据实际情况进行修改
db_config = {
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "database": "kq"
}
# 连接数据库
cnx =pymysql.connect(**db_config)

@app.route('/')
def index():
    return render_template('indexxxx.html')

@app.route('/search')
def search():
    target_student = request.args.get('target_student')
    if target_student:
        similar_students = collaborative_filtering(target_student, top_n=3)
        return render_template('result.html', target_student=target_student, similar_students=similar_students)
    else:
        return render_template('indexxxx.html')


def collaborative_filtering(target_student, top_n=3):
    cursor = cnx.cursor()

    # 查询目标学生的绩点
    query = f"SELECT gpa FROM student WHERE name = '{target_student}'"
    cursor.execute(query)
    target_student_gpa = cursor.fetchone()[0]

    # 查询相似学生的绩点，并按照绩点降序排序 abs 函数是 Python 内置函数，主要作用就是计算数字的绝对值！
    query = f"SELECT name, gpa FROM student WHERE name != '{target_student}' ORDER BY ABS(gpa - {target_student_gpa}), gpa DESC LIMIT {top_n}"
    cursor.execute(query)
    similar_students = cursor.fetchall()

    cursor.close()

    return similar_students

if __name__ == '__main__':
    app.run(host='127.0.0.1', port='5000', debug=True)  # 映射
